﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Timers;
using System.Web;
using XQ.SP.Model;

namespace XM.Comm
{
    public static class TaskAction
    {
        public static DateTime LastTime = DateTime.MinValue;

        public static void Run(object source, ElapsedEventArgs e)
        {
            // 间隔4小时执行一次
            var now = DateTime.Now;
            if (now.Hour >= DateTime.Parse("2020-08-21 06:00:00").Hour && now.Hour <= DateTime.Parse("2020-08-21 22:30:00").Hour)
            {
                if (now > LastTime.AddHours(4.0))
                {
                    QueryUser();
                    LastTime = now;
                }
            }
        }

        public static void QueryUser()
        {
            XQ.SP.BllServices.UserInfoBLL bll = new XQ.SP.BllServices.UserInfoBLL();
            var list = bll.GetModelList();

            if (list.Count > 0)
            {
                var version = DBUtility.DbHelperSQL.GetMaxID("Version", "ReportRecord");

                foreach (var item in list)
                {
                    Calc(item, version + 1);
                }
            }
        }

        private static void Calc(UserInfoItem user, int version)
        {
            // 生成縣鄉村的下拉，根据登录用户

            string city = string.IsNullOrWhiteSpace(user.City) ? null : user.City.Trim(),
                town = string.IsNullOrWhiteSpace(user.Towns) ? null : user.Towns.Trim(),
                village = string.IsNullOrWhiteSpace(user.Village) ? null : user.Village.Trim();

            var where = " ";
            var group = "";
            if (!string.IsNullOrWhiteSpace(village))
            {
                group = "*";
                where = " and Village = '" + village.Trim() + "'";
                // 村就不统计自己了
                return;
            }
            else if (!string.IsNullOrWhiteSpace(town))
            {
                group = "Village";
                where = " and Towns = '" + town.Trim() + "'";
            }
            else if (!string.IsNullOrWhiteSpace(city))
            {
                group = "Towns";
                where = " and Citys = '" + city.Trim() + "'";
            }
            if (string.IsNullOrWhiteSpace(where))
            {
                group = "Citys";
            }

            string sql = @"INSERT INTO dbo.ReportRecord (UserName, AREA, YJRS, LJJC, LJJW, LJXFX, BRJC, BRJW, BRXFX, Version)

";
            // 村不为空就统计自己的
            if (!string.IsNullOrWhiteSpace(village))
            {
                sql += "select  '" + user.UserName + "' AS UserName, '" + village + @"' AS AREA, 
    (select COUNT(*) from dbo.UserInfoCheck where 1=1 {0}) as YJRS,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0}) as LJJC,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0} and  JIWang='是') as LJJW,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0} and  JIWang='否' and JieGuo='阳性') as LJXFX,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0} and DateDiff(dd,CanJiaTime,getdate())=0) as BRJC,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0} and  JIWang='是' and JieGuo='阳性' and DateDiff(dd,CanJiaTime,getdate())=0) as BRJW,
    (select COUNT(*) from dbo.UserInfoCheck where CanJiaJian='是' {0} and  JIWang='否' and JieGuo='阳性' and DateDiff(dd,CanJiaTime,getdate())=0) as BRXFX,
" + version + " AS Version";
                sql = sql.FormatWith(where);
            }
            else
            {
                // 市、县、乡按县、乡、村分组统计
                sql += @"SELECT '" + user.UserName + @"' AS UserName,  A.{0} AS AREA,isnull(YJRS,0) AS YJRS,isnull(LJJC,0) AS LJJC,isnull(LJJW,0) AS LJJW,isnull(LJXFX,0) AS LJXFX,isnull(BRJC,0) AS BRJC,isnull(BRJW,0) AS BRJW,isnull(BRXFX,0) AS BRXFX," + version + @" AS Version FROM (
	--应检人数
	SELECT {0},COUNT({0}) AS YJRS FROM dbo.UserInfoCheck WHERE 1=1 {1} GROUP BY {0}) A
	LEFT JOIN (
	--累计检测
	SELECT {0},COUNT({0}) AS LJJC FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} GROUP BY {0}) B ON B.{0}=A.{0}
	LEFT JOIN (
	--累计既往
	SELECT {0},COUNT({0}) AS LJJW FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} AND JIWang='是' GROUP BY {0}) C ON C.{0}=A.{0}
	LEFT JOIN (
	--累计新发现
	SELECT {0},COUNT({0}) AS LJXFX FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} AND JIWang='否' and JieGuo='阳性' GROUP BY {0}) D ON D.{0}=A.{0}
	LEFT JOIN (
	--本日检测
	SELECT {0},COUNT({0}) AS BRJC FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} AND DateDiff(dd,CanJiaTime,getdate())=0 GROUP BY {0}) E ON E.{0}=A.{0}
	LEFT JOIN (
	--本日既往
	SELECT {0},COUNT({0}) AS BRJW FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} AND JIWang='是' AND JieGuo='阳性' AND DateDiff(dd,CanJiaTime,getdate())=0 GROUP BY {0}) F ON F.{0}=A.{0}
	LEFT JOIN (
	--本日新发现
	SELECT {0},COUNT({0}) AS BRXFX FROM dbo.UserInfoCheck WHERE CanJiaJian='是' {1} AND JIWang='否' AND JieGuo='阳性' AND DateDiff(dd,CanJiaTime,getdate())=0 GROUP BY {0}) G ON G.{0}=A.{0}";
                sql = sql.FormatWith(group, where);
            }

            DBUtility.DbHelperSQL.ExecuteSql(sql);
        }
    }
}